package com.mbc.rmt.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.mbc.common.dao.DAO;
import com.mbc.common.param.CriteriaParams;
import com.mbc.rmt.data.General_COADataList;
import com.mbc.rmt.entities.General_COA;

public class General_COADAOImpls extends DAO<General_COA> implements General_COADAO{
	
	public General_COADAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "General_COA";
	}
	
	protected void updateRecord(PreparedStatement pStmt, General_COA info)
			throws SQLException {

		int i = 1;
		pStmt.setLong(i++, info.getCR_GeneralAccountSetID());
		pStmt.setLong(i++, info.getLiabilitiesAccountID());
		pStmt.setLong(i++, info.getCashAccountID());
		pStmt.setLong(i++, info.getExchangeRoundingErrorAccountID());
		pStmt.setLong(i++, info.getExtraFeesAccountID());
		pStmt.setLong(i++, info.getLiabilitiesDepartmentID());
		pStmt.setLong(i++, info.getCashDepartmentID());
		pStmt.setLong(i++, info.getExchangeRoundingErrorDepartmentID());
		pStmt.setLong(i++, info.getExtraFessDepartmentID());
		pStmt.setLong(i++, info.getBranch_id());
	}

	@Override
	public void create(General_COA entity) throws SQLException {
		query = "insert into "+ table_name + " ( CR_GeneralAccountSetID,LiabilitiesAccountID,CashAccountID,ExchangeRoundingErrorAccountID,ExtraFeesAccountID,LiabilitiesDepartmentID,CashDepartmentID,ExchangeRoundingErrorDepartmentID,ExtraFeesDepartmentID,BranchId) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		updateRecord(pstmt, entity);
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public void update(General_COA entity) throws SQLException {
		query = "update " + table_name + " set " +
				" LiabilitiesAccountID=?,CashAccountID=?,ExchangeRoundingErrorAccountID=?,ExtraFeesAccountID=?,LiabilitiesDepartmentID=?,CashDepartmentID=?,ExchangeRoundingErrorDepartmentID=?,ExtraFeesDepartmentID=?,BranchId=?" +
					" where CR_GeneralAccountSetID = " + entity.getCR_GeneralAccountSetID();
		pstmt = conn.prepareStatement(query);
//		updateRecord(pstmt, entity);
		int i = 1;
		pstmt.setLong(i++, entity.getLiabilitiesAccountID());
		pstmt.setLong(i++, entity.getCashAccountID());
		pstmt.setLong(i++, entity.getExchangeRoundingErrorAccountID());
		pstmt.setLong(i++, entity.getExtraFeesAccountID());
		pstmt.setLong(i++, entity.getLiabilitiesDepartmentID());
		pstmt.setLong(i++, entity.getCashDepartmentID());
		pstmt.setLong(i++, entity.getExchangeRoundingErrorDepartmentID());
		pstmt.setLong(i++, entity.getExtraFessDepartmentID());
		pstmt.setLong(i++, entity.getBranch_id());
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where 1 = 1 and CR_GeneralAccountSetID = "+ key ;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public General_COA find(long key) throws SQLException {
		General_COA l_General_COA = null;
		//query = getSelectQuery() + " where CR_GeneralAccountSetID = "+key;
		
		query = "select "+
				"cgcoa.CR_GeneralAccountSetID, "+
				"cgcoa.LiabilitiesAccountID,lcoa.AC_ID Liabilities_ACCode, "+
				"cgcoa.LiabilitiesDepartmentID,ldept.departmentCode Liabilities_DeptCode, "+
				"cgcoa.CashAccountID,ccoa.AC_ID Cash_ACCode, "+
				"cgcoa.CashDepartmentID,cdept.departmentCode Cash_DeptCode, "+
				"cgcoa.ExchangeRoundingErrorAccountID,erecoa.AC_ID ExchangeRoundingError_ACCode, "+
				"cgcoa.ExchangeRoundingErrorDepartmentID,eredept.departmentCode ExchangeRoundingError_DeptCode, "+
				"cgcoa.ExtraFeesAccountID,efcoa.AC_ID ExtraFees_ACCode, "+
				"cgcoa.ExtraFeesDepartmentID,efdept.departmentCode ExtraFees_DeptCode "+ 
				"from General_COA cgcoa " +
				"LEFT JOIN dbo.COA lcoa ON cgcoa.LiabilitiesAccountID =  lcoa.COAKey " +
				"LEFT JOIN dbo.Department ldept ON cgcoa.LiabilitiesDepartmentID = ldept.DepartmentID " + 
				"LEFT JOIN dbo.COA ccoa ON cgcoa.CashAccountID = ccoa.COAKey " +
				"LEFT JOIN dbo.Department cdept ON cgcoa.CashDepartmentID = cdept.DepartmentID " +
				"LEFT JOIN dbo.COA erecoa ON cgcoa.ExchangeRoundingErrorAccountID = erecoa.COAKey " + 
				"LEFT JOIN dbo.Department eredept ON cgcoa.ExchangeRoundingErrorDepartmentID =  eredept.DepartmentID " +  
				"LEFT JOIN dbo.COA efcoa ON cgcoa.ExtraFeesAccountID =  efcoa.COAKey " +
				"LEFT JOIN dbo.Department efdept ON cgcoa.ExtraFeesDepartmentID =  efdept.DepartmentID where 1 = 1" +
				" and cgcoa.BranchId = " + key;
		pstmt =	conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			l_General_COA = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_General_COA;
	}
	@Override
	public General_COADataList findGeneral_COAs(CriteriaParams cri)
			throws SQLException {
		General_COADataList l_DataList = new General_COADataList();
		ArrayList<General_COA> l_entityList = new ArrayList<General_COA>();
		String l_cri = " and cgcoa.BranchId = " + cri.getBranchId();
		query = "set rowcount " + cri.getSize() +
				" select * from (select row_number() over (order by CR_GeneralAccountSetID)as [rowno], "+
				"cgcoa.CR_GeneralAccountSetID, "+
				"cgcoa.LiabilitiesAccountID,lcoa.AC_ID Liabilities_ACCode, "+
				"cgcoa.LiabilitiesDepartmentID,ldept.departmentCode Liabilities_DeptCode, "+
				"cgcoa.CashAccountID,ccoa.AC_ID Cash_ACCode, "+
				"cgcoa.CashDepartmentID,cdept.departmentCode Cash_DeptCode, "+
				"cgcoa.ExchangeRoundingErrorAccountID,erecoa.AC_ID ExchangeRoundingError_ACCode, "+
				"cgcoa.ExchangeRoundingErrorDepartmentID,eredept.departmentCode ExchangeRoundingError_DeptCode, "+
				"cgcoa.ExtraFeesAccountID,efcoa.AC_ID ExtraFees_ACCode, "+
				"cgcoa.ExtraFeesDepartmentID,efdept.departmentCode ExtraFees_DeptCode "+ 
			"from General_COA cgcoa "+
			"LEFT JOIN dbo.COA lcoa ON cgcoa.LiabilitiesAccountID =  lcoa.COAKey "+ 
			"LEFT JOIN dbo.Department ldept ON cgcoa.LiabilitiesDepartmentID = ldept.departmentID "+ 
			"LEFT JOIN dbo.COA ccoa ON cgcoa.CashAccountID = ccoa.COAKey "+ 
			"LEFT JOIN dbo.Department cdept ON cgcoa.CashDepartmentID = cdept.departmentID "+  
			"LEFT JOIN dbo.COA erecoa ON cgcoa.ExchangeRoundingErrorAccountID = erecoa.COAKey "+
			"LEFT JOIN dbo.Department eredept ON cgcoa.ExchangeRoundingErrorDepartmentID =  eredept.departmentID "+ 
			"LEFT JOIN dbo.COA efcoa ON cgcoa.ExtraFeesAccountID =  efcoa.COAKey "+
			"LEFT JOIN dbo.Department efdept ON cgcoa.ExtraFeesDepartmentID =  efdept.departmentID "+ 
			/*"where 1 = 1" + l_cri + ") " +*/" where 1 = 1"+l_cri+" ) " +
				table_name+ " where rowno > "+cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while(rs.next()){
				l_entityList.add(readRecord(rs));
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		/*String l_cri1 = " and (office_coa_code like '%" +cri.getKeyword() +"%'"+
				" or description like '%" + cri.getKeyword() + "%')";
		query = "select count(office_coa_code) from "+table_name+" where 1 = 1"+ l_cri1;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()){
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();*/
		return l_DataList;
	}

	@Override
	protected General_COA readRecord(ResultSet result) throws SQLException {
		General_COA l_info = new General_COA();
		l_info.setCR_GeneralAccountSetID(result.getLong("CR_GeneralAccountSetID"));
		
		l_info.setLiabilitiesAccountID(result.getLong("LiabilitiesAccountID"));
		l_info.setLiabilities_ACCode(result.getString("Liabilities_ACCode"));
		l_info.setLiabilitiesDepartmentID(result.getLong("LiabilitiesDepartmentID"));
		l_info.setLiabilities_DeptCode(result.getString("Liabilities_DeptCode"));
		
		l_info.setCashAccountID(result.getLong("CashAccountID"));
		l_info.setCash_ACCode(result.getString("Cash_ACCode"));
		l_info.setCashDepartmentID(result.getLong("CashDepartmentID"));
		l_info.setCash_DeptCode(result.getString("Cash_DeptCode"));
			
		l_info.setExchangeRoundingErrorAccountID(result.getLong("ExchangeRoundingErrorAccountID"));
		l_info.setExchangeRoundingError_ACCode(result.getString("ExchangeRoundingError_ACCode"));
		l_info.setExchangeRoundingErrorDepartmentID(result.getLong("ExchangeRoundingErrorDepartmentID"));
		l_info.setExchangeRoundingError_DeptCode(result.getString("ExchangeRoundingError_DeptCode"));
		
		l_info.setExtraFeesAccountID(result.getLong("ExtraFeesAccountID"));
		l_info.setExtraFees_ACCode(result.getString("ExtraFees_ACCode"));
		l_info.setExtraFessDepartmentID(result.getLong("ExtraFeesDepartmentID"));
		l_info.setExtraFees_DeptCode(result.getString("ExtraFees_DeptCode"));

		return l_info;
	}

}
